
package com.qjz.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.qjz.entity.HigherGradeTo;
import com.qjz.entity.StudentInfoTo;

public final class ExcelUtil {

	private ExcelUtil() {

	}

	private static void createCell(HSSFCellStyle style1, HSSFRow row,
		int j, int age) {
		HSSFCell avgNum1 = row.createCell(j);
		avgNum1.setCellValue(age);
		avgNum1.setCellStyle(style1);
	}
	
	/**
	 * 保存excel并返回保存路径
	 * 
	 * @param wb
	 *                excel
	 * @param loginId
	 *                登陆者ID
	 * @return 保存路径
	 */
	public static String saveGeneratedExcel(HSSFWorkbook wb, Long loginId, String fileTitle) {
		java.util.Date date = new java.util.Date();
		String path =
			"\\excel\\" + fileTitle + "_" + loginId
			+ date.getTime() + ".xls";
		String fullPath = FileOperate.getDir("saveDir") + path;
		File file = new File(fullPath);
		try {
			if (file != null && !file.exists()) {
				file.getParentFile().mkdirs();
				file.createNewFile();
			}
			FileOutputStream fos = new FileOutputStream(file);
			wb.write(fos);
			fos.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return path;
	}
	
	public static HSSFWorkbook hgDataExcel(List<HigherGradeTo> dataList){
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("Sheet1");
		int rowNum = 0;
		// 设置居中式样
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 设置字体--黑体
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		//titleCell.setCellStyle(style);
		HSSFCellStyle styleBold = wb.createCellStyle();
		styleBold.setFont(font);			

		HSSFCellStyle dateStyle = wb.createCellStyle();
		HSSFDataFormat format = wb.createDataFormat();
		dateStyle.setDataFormat(format.getFormat("dd-MM-yyyy"));
		
		HSSFRow topRow1 = sheet.createRow(rowNum++);

		String[] arr = {"姓名", "原学校", "班级", "施教区", "验证", "状态"};
		for (int i = 0; i < arr.length; i++) {
			HSSFCell temp = topRow1.createCell(i);
			temp.setCellValue(arr[i].toString());
		}
		
		if (null != dataList && !dataList.isEmpty()) {			
			for(HigherGradeTo hg : dataList){
				HSSFRow row = sheet.createRow(rowNum++);
				int j = 0;
				HSSFCell avgNum0 = row.createCell(j++);
				avgNum0.setCellValue(hg.getXingMing());
				
				HSSFCell avgNum1 = row.createCell(j++);
				avgNum1.setCellValue(hg.getYuanXueXiao());
				
				HSSFCell avgNum2 = row.createCell(j++);
				avgNum2.setCellValue(hg.getBanJi());
				
				HSSFCell avgNum3 = row.createCell(j++);
				avgNum3.setCellValue(hg.getXinXiaoMing());
				
				HSSFCell avgNum4 = row.createCell(j++);
				avgNum4.setCellValue(hg.getYanZheng());
				
				HSSFCell avgNum5 = row.createCell(j++);
				avgNum5.setCellValue(hg.getZhuangTaiMing());
			}
		}
		return wb;
	}
	public static HSSFWorkbook studentDataExcel(List<StudentInfoTo> dataList){
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("Sheet1");
		int rowNum = 0;
		// 设置居中式样
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 设置字体--黑体
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		//titleCell.setCellStyle(style);
		HSSFCellStyle styleBold = wb.createCellStyle();
		styleBold.setFont(font);			

		HSSFCellStyle dateStyle = wb.createCellStyle();
		HSSFDataFormat format = wb.createDataFormat();
		dateStyle.setDataFormat(format.getFormat("dd-MM-yyyy"));
		
		HSSFRow topRow1 = sheet.createRow(rowNum++);

		String[] arr = {"姓名", "曾用名", "性别", "民族", "出生年月", "入学年份", "班级", "学校", "学籍号",
				"身份证号", "国籍", "港澳台侨胞", "户籍所在地", "家庭常住地区", "家庭常住地详细地址", 
				"户籍地教育主管部门", "学习简历", "学籍状态", "市民卡号", "户口所在派出所", "家庭住址", "生源性质", 
				"房产证信息", "房产证产权人", "是否低保户或低保边缘户", "低保户或低保边缘户证件号",
				"是否特殊病例学生", "特殊病例名称", "第一监护人称谓", "第一监护人姓名", "第一监护人工作单位", 
				"第一监护人职务", "第一监护人联系电话", "第二监护人称谓", "第二监护人姓名", "第二监护人工作单位", 
				"第二监护人职务", "第二监护人联系电话", "入学方式", "是否孤儿", "是否寄宿", "是否留守儿童", "是否残疾"};
		for (int i = 0; i < arr.length; i++) {
			HSSFCell temp = topRow1.createCell(i);
			temp.setCellValue(arr[i].toString());
		}
		
		if (null != dataList && !dataList.isEmpty()) {			
			for(StudentInfoTo student : dataList){
				HSSFRow row = sheet.createRow(rowNum++);
				int j = 0;
				HSSFCell avgNum0 = row.createCell(j++);
				avgNum0.setCellValue(student.getXingMing());
				
				HSSFCell avgNum1 = row.createCell(j++);
				avgNum1.setCellValue(student.getCengYongMing());
				
				HSSFCell avgNum2 = row.createCell(j++);
				avgNum2.setCellValue(student.getXingBie());
				
				HSSFCell avgNum3 = row.createCell(j++);
				avgNum3.setCellValue(student.getMinZu());
				
				HSSFCell avgNum42 = row.createCell(j++);
				avgNum42.setCellValue(student.getChuShengRiQi());
				
				HSSFCell avgNum4 = row.createCell(j++);
				avgNum4.setCellValue(student.getRuXueNian());
				
				HSSFCell avgNum5 = row.createCell(j++);
				avgNum5.setCellValue(student.getBanJi());
				
				HSSFCell avgNum6 = row.createCell(j++);
				avgNum6.setCellValue(student.getXueXiao());
				
				HSSFCell avgNum7 = row.createCell(j++);
				avgNum7.setCellValue(student.getXueJiHao());
				
				HSSFCell avgNum8 = row.createCell(j++);
				avgNum8.setCellValue(student.getShenFenZheng());
				
				HSSFCell avgNum9 = row.createCell(j++);
				avgNum9.setCellValue(student.getGuoJi());
				
				HSSFCell avgNum10 = row.createCell(j++);
				avgNum10.setCellValue(student.getQiaoBao());
				
				HSSFCell avgNum11 = row.createCell(j++);
				avgNum11.setCellValue(student.getHuJiDiZhi());
				
				HSSFCell avgNum12 = row.createCell(j++);
				avgNum12.setCellValue(student.getChangZhuDiQu());
				
				HSSFCell avgNum13 = row.createCell(j++);
				avgNum13.setCellValue(student.getXiangXiDiZhi());
				
				HSSFCell avgNum14 = row.createCell(j++);
				avgNum14.setCellValue(student.getJiaoYuZhuGuan());
				
				HSSFCell avgNum15 = row.createCell(j++);
				avgNum15.setCellValue(student.getXueXiJianLi());
				
				HSSFCell avgNum16 = row.createCell(j++);
				avgNum16.setCellValue(student.getXueJiZhuangTai());
				
				HSSFCell avgNum17 = row.createCell(j++);
				avgNum17.setCellValue(student.getShiMinKa());
				
				HSSFCell avgNum18 = row.createCell(j++);
				avgNum18.setCellValue(student.getPaiChuSuo());
				
				HSSFCell avgNum19 = row.createCell(j++);
				avgNum19.setCellValue(student.getJiaTingZhuZhi());
				
				HSSFCell avgNum20 = row.createCell(j++);
				avgNum20.setCellValue(student.getShengYuanXingZhi());
				
				HSSFCell avgNum21 = row.createCell(j++);
				avgNum21.setCellValue(student.getFangChanZheng());
				
				HSSFCell avgNum22 = row.createCell(j++);
				avgNum22.setCellValue(student.getChanQuanRen());
				
				HSSFCell avgNum23 = row.createCell(j++);
				avgNum23.setCellValue(student.getDiBaoHu());
				
				HSSFCell avgNum24 = row.createCell(j++);
				avgNum24.setCellValue(student.getDiBaoZheng());
				
				HSSFCell avgNum25 = row.createCell(j++);
				avgNum25.setCellValue(student.getTeShuBingLi());
				
				HSSFCell avgNum26 = row.createCell(j++);
				avgNum26.setCellValue(student.getBingLiMing());
				
				HSSFCell avgNum27 = row.createCell(j++);
				avgNum27.setCellValue(student.getYiChengWei());
				
				HSSFCell avgNum28 = row.createCell(j++);
				avgNum28.setCellValue(student.getYiXingMing());
				
				HSSFCell avgNum29 = row.createCell(j++);
				avgNum29.setCellValue(student.getYiDanWei());

				HSSFCell avgNum30 = row.createCell(j++);
				avgNum30.setCellValue(student.getYiZhiWu());
				
				HSSFCell avgNum31 = row.createCell(j++);
				avgNum31.setCellValue(student.getYiDianHua());
				
				HSSFCell avgNum32 = row.createCell(j++);
				avgNum32.setCellValue(student.getErChengWei2());
				
				HSSFCell avgNum33 = row.createCell(j++);
				avgNum33.setCellValue(student.getErXingMing2());
				
				HSSFCell avgNum34 = row.createCell(j++);
				avgNum34.setCellValue(student.getErDanWei2());
				
				HSSFCell avgNum35 = row.createCell(j++);
				avgNum35.setCellValue(student.getErZhiWu2());
				
				HSSFCell avgNum36 = row.createCell(j++);
				avgNum36.setCellValue(student.getErDianHua2());
				
				HSSFCell avgNum37 = row.createCell(j++);
				avgNum37.setCellValue(student.getRuXueFangShi());

				HSSFCell avgNum38 = row.createCell(j++);
				avgNum38.setCellValue(student.getGuEr());
				
				HSSFCell avgNum39 = row.createCell(j++);
				avgNum39.setCellValue(student.getJiSu());
				
				HSSFCell avgNum40 = row.createCell(j++);
				avgNum40.setCellValue(student.getLiuShou());
				
				HSSFCell avgNum41 = row.createCell(j++);
				avgNum41.setCellValue(student.getCanJi());
			}
		}
		return wb;
	}
}
